from openpyxl import Workbook #openpyxl
from openpyxl import load_workbook
from openpyxl.styles import fills,colors,NamedStyle,Font,Side,Border,PatternFill,Alignment,Protection
import configparser
import easygui
import pathlib
import os
import sys
import sqlite3
from inc import *
root=pathlib.Path(os.path.dirname(os.path.realpath(sys.argv[0])))
db=sqlite3.connect(str(root/"yj.db"))
cur = db.cursor()


sql="select bj from main group by bj"
r=cur.execute(sql)
bj=[]
for i in r.fetchall():
    bj.append(i[0])
print(bj)
wb = Workbook()
ws=wb.active

bd = Border(
    left=Side(border_style="thin",
                color='000000'),
    right=Side(border_style="thin",
                color='000000'),
    top=Side(border_style="thin",
                color='000000'),
    bottom=Side(border_style="thin",
                color='000000')
                )
bd1 = Border(
    left=Side(border_style="thin",
                color='000000'),
    right=Side(border_style="thin",
                color='000000'),
    top=Side(border_style="thin",
                color='000000')
                )
bd2 = Border(
    left=Side(border_style="thin",
                color='000000'),
    right=Side(border_style="thin",
                color='000000'),
    bottom=Side(border_style="thin",
                color='000000')
                )
alignment=Alignment(horizontal='center',#水平'center', 'centerContinuous', 'justify', 'fill', 'general', 'distributed', 'left', 'right'
            vertical='top',#垂直'distributed', 'bottom', 'top', 'center', 'justify'
            text_rotation=0,#旋转角度0~180
            wrap_text=False,#文字换行
            shrink_to_fit=True,#自适应宽度，改变文字大小,上一项false
            indent=0)
alignment1=Alignment(horizontal='center',#水平'center', 'centerContinuous', 'justify', 'fill', 'general', 'distributed', 'left', 'right'
            vertical='center',#垂直'distributed', 'bottom', 'top', 'center', 'justify'
            text_rotation=0,#旋转角度0~180
            wrap_text=False,#文字换行
            shrink_to_fit=True,#自适应宽度，改变文字大小,上一项false
            indent=0)
for i in bj:
    n=6
    i1=getAlias(str(i[2:]))+"班"
    ws = wb.create_sheet(i1)
    #ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE #横向页面
    ws.page_setup.paperSize = ws.PAPERSIZE_A4#纸张大小
    #ws.print_title_rows = '1:2'#设置打印标题为2行，每页开始都打印sheet的前两行
    ws.page_margins.left = 0
    ws.page_margins.right = 0
    ws.page_margins.top = 0.25
    ws.page_margins.bottom = 0
    ws.page_margins.header = 0
    ws.page_margins.footer = 0#设置页边距
    ws.print_options.horizontalCentered = True #水平剧中
    sql="select kh,name,kc,z,p from main where bj = '{bj}'".format(bj=i)
    r=cur.execute(sql)
    ws.column_dimensions['A'].width = 15
    ws.column_dimensions['B'].width = 7
    ws.column_dimensions['C'].width = 9
    ws.column_dimensions['D'].width = 7
    ws.column_dimensions['E'].width = 7
    ws.column_dimensions['F'].width = 3
    ws.column_dimensions['G'].width = 15
    ws.column_dimensions['H'].width = 7
    ws.column_dimensions['I'].width = 9
    ws.column_dimensions['J'].width = 7
    ws.column_dimensions['K'].width = 7
    ws.cell(2,1,"考号").border=bd
    ws.cell(2,2,"姓名").border=bd
    ws.cell(2,3,"考场").border=bd
    ws.cell(2,4,"组").border=bd
    ws.cell(2,5,"排").border=bd
    ws.cell(2,7,"考号").border=bd
    ws.cell(2,8,"姓名").border=bd
    ws.cell(2,9,"考场").border=bd
    ws.cell(2,10,"组").border=bd
    ws.cell(2,11,"排").border=bd
    for i in range(1,12):
        ws.cell(2,i).alignment=alignment
        ws.cell(2,i).font=Font(bold=True)
    ws.merge_cells('A1:K1')
    ws.cell(1,1,"{0}学生名单".format(i1)).alignment=alignment
    ws.cell(1,1).font=Font(size=20,bold=True,)
    ws.row_dimensions[1].height = 27
    for s in r.fetchall():
        ws.cell(int(n/2),((n%2)*6)+1,s[0]).border=bd
        ws.cell(int(n/2),((n%2)*6)+2,s[1]).border=bd
        ws.cell(int(n/2),((n%2)*6)+3,getExamAlias(s[2])+"考场").border=bd
        ws.cell(int(n/2),((n%2)*6)+4,"第{z}组".format(z=int((s[3]+1)/2))).border=bd
        ws.cell(int(n/2),((n%2)*6)+5,"第{p}排".format(p=s[4])).border=bd
        n=n+1


wb.remove(wb["Sheet"])
t=4
while(t):
    try:
        wb.save(root/'output/班级名单.xlsx')
    except:
        easygui.exceptionbox("请关闭 'output/班级名单.xlsx' 文件")
    finally:
        t=t-1
wb=None
ws=None

wb = Workbook()

sql="select kc from main group by kc"
r=cur.execute(sql)
for kc in r.fetchall():
    kct=getExamAlias(str(kc[0]))
    ws = wb.create_sheet(kct)
    ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE #横向页面
    ws.page_setup.paperSize = ws.PAPERSIZE_A4#纸张大小
    #ws.print_title_rows = '1:2'#设置打印标题为2行，每页开始都打印sheet的前两行
    ws.page_margins.left = 0
    ws.page_margins.right = 0
    ws.page_margins.top = 0.25
    ws.page_margins.bottom = 0
    ws.page_margins.header = 0
    ws.page_margins.footer = 0#设置页边距
    ws.print_options.horizontalCentered = True #水平剧中
    
    r=cur.execute(f"select max(z) from main where kc='{kc[0]}'")
    zl=r.fetchall()
    l=zl[0][0]+int(zl[0][0]%2)#获取最大小组数，取偶数
    if(l<=6):#小于6组以6组计算
        l=6
        lt=8
    else:
        lt=l+(l/2)-1
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=int(lt))
    ws.cell(1,1,"{0}考场学生名单".format(kct)).alignment=alignment
    ws.cell(1,1).font=Font(size=20,bold=True)
    #l=len(zl)
    '''
    if(l%2==1):l=l+1
    if(l<=2):
        l=3
    else:
        l=int(l/2)
    '''
    lt=int(l/2)
    ws.cell(2,int(l/2)+1,"讲台").alignment=alignment
    ws.merge_cells(start_row=2, start_column=int(lt+1), end_row=2, end_column=int(lt*2-1))
    for i in range(int(lt+1),int(lt*2)):
        ws.cell(2,i).border=bd
    ws.cell(2,lt+1).font=Font(size=20,bold=True)

    ws.row_dimensions[1].height = 27
    ws.row_dimensions[2].height = 27
    ws.row_dimensions[3].height = 5
    ws.row_dimensions[4].height = 27
    s="ABCDEFGHIJKLMNOPQRST"
    n=1
    for i in s :
        if(n%3==0):
            ws.column_dimensions[i].width = 3
        else:
            ws.column_dimensions[i].width = 10
        n=n+1
    startRow=4
    zn=int(l/2)#桌子列数
    for i in range(0,zn):
        #print(zn-i)
        ws.merge_cells(start_row=startRow, start_column=int(i*3+1), end_row=startRow, end_column=int(i*3+2))
        ws.cell(startRow,i*3+1,"第{0}组".format(dx[int(zn-i)])).alignment=alignment
        ws.cell(startRow,i*3+1).font=Font(size=18)
        ws.cell(startRow,i*3+1).border=bd
        ws.cell(startRow,i*3+2).border=bd
    ws.cell(startRow-2,zn*3-1,"门").alignment=alignment
    ws.cell(startRow-2,zn*3-1).font=Font(size=18)
    ws.cell(startRow-2,zn*3-1).border=bd
    
    r=cur.execute(f"select nj,bj,name,z,p from main where kc='{kc[0]}'order by p")
    n=0
    for info in r.fetchall():
        #print(info)
        if(info[3]==0):
            zt=n%6+1
            pt=int(n/6)+1
            n=n+1
        else:
            zt=info[3]
            pt=info[4]
        colt=int((zn-int((zt+1)/2))*3+1+int(zt%2))
        rowt=startRow+pt*2-1
        ws.cell(rowt,colt,"{0}班".format(getAlias(str(info[1][2:])))).border=bd1
        ws.cell(rowt,colt).alignment=alignment
        rowt=rowt+1
        ws.cell(rowt,colt,info[2]).border=bd2
        ws.cell(rowt,colt).alignment=alignment
        ws.row_dimensions[rowt].height = 15
    print("{0}考场已导出".format(kc))
wb.remove(wb["Sheet"])   

t=4
while(t):
    try:
        wb.save(root/'output/考场分布.xlsx')
    except:
        easygui.exceptionbox("请关闭 'output/考场分布.xlsx' 文件")
    finally:
        t=t-1

########################### 考场人数统计
wb = Workbook()
ws=wb.active
ws.column_dimensions['A'].width = 16
ws.column_dimensions['B'].width = 5
ws.column_dimensions['C'].width = 8
ws.column_dimensions['D'].width = 16
ws.column_dimensions['E'].width = 5
ws.column_dimensions['F'].width = 8
ws.column_dimensions['G'].width = 16
ws.column_dimensions['H'].width = 5
ws.cell(1,1,"考场")

result=cur.execute("select kc from main group by kc")
result=result.fetchall()
grade=cur.execute("select nj from main group by nj")
grade=grade.fetchall()
row=1
col=1
ws.cell(row,col,"考场")
for i in grade:
    ws.merge_cells(start_row=row, start_column=col, end_row=row, end_column=col+1)
    ws.cell(row,col,dx[int(i[0])]+"年级").border=bd
    ws.cell(row,col).font=Font(size=24,bold=True,)
    ws.cell(row,col+1).border=bd
    ws.cell(row,col).font=Font(size=24,bold=True,)
    ws.cell(row,col).alignment=alignment1
    col=col+3

for kch in result:
    row=row+1
    col=-1
    
    for njh in grade:
        col=col+2
        ws.cell(row,col,getAlias(kch[0])+"考场").border=bd
        ws.cell(row,col).font=Font(size=20,bold=False,)
        count=cur.execute("select count(*) from main where kc='{0}' and nj='{1}'".format(kch[0],njh[0]))
        count=count.fetchone()
        col=col+1
        ws.cell(row,col,count[0]).border=bd
        ws.cell(row,col).font=Font(size=20,bold=False,)

t=4
while(t):
    try:
        wb.save(root/'output/考生数.xlsx')
    except:
        easygui.exceptionbox("请关闭 'output/考生数.xlsx' 文件")
    finally:
        t=t-1
print(root/"output/考生数.xlsx")
wb=None
ws=None